import configparser
from bean.hot_new import HotNew
from bean.hot_new_message import HotNewMessage
from typing import List
import pymysql
import sys

from bean.hot_new_type import HotNewType


def query(hot_news_type):
    news_list: List[HotNewMessage] = []
    cursor = None
    try:
        conn = get_conn()
        sql = '''select ht.type, hn.serial_no, hn.title, hn.link, ht.type_name, hn.ext_info, hn.excerpt, 
                    ht.color, ht.app_id, hn.app_path, ht.img_id
                    from hot_news hn left join hot_news_type ht on hn.type=ht.type where 1=1 '''
        if hot_news_type is not None:
            sql = sql + " and ht.type='" + hot_news_type + "' "
        sql = sql + ''' order by hn.type, serial_no'''
        cursor = conn.cursor()
        conn.begin()
        cursor.execute(sql)
        db_data_list = cursor.fetchall()
        hot_news: List[HotNew] = []
        pre_type_name = ""
        for db_data in db_data_list:
            type_name = db_data[4]
            if type_name != pre_type_name:
                pre_type_name = type_name
                pre_type_id = db_data[0]
                if len(hot_news) > 0:
                    message = HotNewMessage(hot_news)
                    message.new_type = pre_type_name
                    message.new_type_id = pre_type_id
                    message.app_id = db_data[8]
                    message.app_path = db_data[9]
                    news_list.append(message)
                hot_news = list()
            new = HotNew(db_data[1], db_data[2], db_data[3], db_data[4], db_data[5], db_data[6])
            new.type_id = db_data[0]
            new.type_color = db_data[7]
            new.app_id = db_data[8]
            new.app_path = db_data[9]
            new.img_path = db_data[10]
            hot_news.append(new)

        if len(hot_news) > 0:
            message = HotNewMessage(hot_news)
            news_list.append(message)
        conn.commit()
    except Exception as e:
        print(e)
    if cursor is not None:
        cursor.close()
    return news_list


def save(news: HotNewMessage):
    db_news = []
    delete_type = ''
    hot_news: List[HotNew] = news.hot_news
    for new in hot_news:
        delete_type = new.new_type
        db_news.append([new.serial_no, new.new_type, new.title, new.link, new.ext_info, new.excerpt, new.app_path])
    conn = get_conn()
    sql = '''insert into hot_news(serial_no, type, title, link, ext_info, excerpt, app_path) 
                values (%s, %s, %s, %s, %s, %s, %s)'''
    cursor = conn.cursor()
    conn.begin()
    cursor.execute("delete from hot_news where type={0}".format(delete_type))
    cursor.executemany(sql, db_news)
    conn.commit()
    cursor.close()


def query_all_types():
    conn = None
    cursor = None
    result = []
    try:
        conn = get_conn()
        cursor = conn.cursor()
        conn.begin()
        cursor.execute("select type, type_name, color, app_id, push_enable from hot_news_type where push_enable=1")
        db_data_list = cursor.fetchall()
        for db_data in db_data_list:
            result.append(HotNewType(db_data[0], db_data[1], db_data[2], db_data[3], db_data[4]))
        conn.commit()
    except Exception as e:
        if conn is not None:
            conn.rollback()
        print(e)
    if cursor is not None:
        cursor.close()
    return result


def get_conn():
    cf = configparser.ConfigParser()
    cf.read(sys.path[0] + "/conf/db.properties")
    conn = pymysql.connect(
        host=cf.get("mysql", "host"),
        user=cf.get("mysql", "user"),
        passwd=cf.get("mysql", "passwd"),
        db=cf.get("mysql", "db"),
        port=int(cf.get("mysql", "port")),
        charset=cf.get("mysql", "charset"))
    return conn
